package com.example.memosystem.mapper;

import com.example.memosystem.model.Memo;
import org.apache.ibatis.annotations.*;

import java.util.List;

@Mapper
public interface MemoMapper {
    
    @Insert("INSERT INTO memo (user_id, category_id, title, content, create_time, update_time) VALUES (#{userId}, #{categoryId}, #{title}, #{content}, #{createTime}, #{updateTime})")
    @Options(useGeneratedKeys = true, keyProperty = "id")
    int insertMemo(Memo memo);

    @Update("<script>UPDATE memo SET " +
            "<if test='title != null'>title = #{title},</if> " +
            "<if test='content != null'>content = #{content},</if> " +
            "<if test='categoryId != null'>category_id = #{categoryId},</if> " +
            "update_time = NOW() " +
            "WHERE id = #{id} AND user_id = #{userId}</script>")
    int updateMemo(Memo memo);
    
    @Delete("DELETE FROM memo WHERE id = #{id} AND user_id = #{userId}")
    int deleteMemo(@Param("id") Integer id, @Param("userId") Integer userId);
    
    @Update("UPDATE memo SET delete_time = NOW() WHERE id = #{id} AND user_id = #{userId}")
    int moveToTrash(@Param("id") Integer id, @Param("userId") Integer userId);
    
    @Update("UPDATE memo SET delete_time = NULL WHERE id = #{id} AND user_id = #{userId}")
    int restoreMemo(@Param("id") Integer id, @Param("userId") Integer userId);
    
    @Select("SELECT m.id, m.user_id, m.category_id, m.title, m.content, m.create_time, m.update_time, m.delete_time, " +
            "c.id as 'category.id', c.name as 'category.name', c.color as 'category.color' " +
            "FROM memo m " +
            "LEFT JOIN category c ON m.category_id = c.id " +
            "WHERE m.id = #{id} AND m.user_id = #{userId}")
    Memo findById(@Param("id") Integer id, @Param("userId") Integer userId);
    
    @Select("SELECT m.id, m.user_id, m.category_id, m.title, m.content, m.create_time, m.update_time, m.delete_time, " +
            "c.id as 'category.id', c.name as 'category.name', c.color as 'category.color' " +
            "FROM memo m " +
            "LEFT JOIN category c ON m.category_id = c.id " +
            "WHERE m.user_id = #{userId} AND m.delete_time IS NULL " +
            "ORDER BY m.update_time DESC")
    List<Memo> findAllByUserId(Integer userId);
    
    @Select("SELECT m.id, m.user_id, m.category_id, m.title, m.content, m.create_time, m.update_time, m.delete_time, " +
            "c.id as 'category.id', c.name as 'category.name', c.color as 'category.color' " +
            "FROM memo m " +
            "LEFT JOIN category c ON m.category_id = c.id " +
            "WHERE m.user_id = #{userId} AND m.delete_time IS NULL AND m.title LIKE CONCAT('%', #{keyword}, '%') " +
            "ORDER BY m.update_time DESC")
    List<Memo> searchByTitle(@Param("userId") Integer userId, @Param("keyword") String keyword);
    
    @Select("SELECT m.id, m.user_id, m.category_id, m.title, m.content, m.create_time, m.update_time, m.delete_time, " +
            "c.id as 'category.id', c.name as 'category.name', c.color as 'category.color' " +
            "FROM memo m " +
            "LEFT JOIN category c ON m.category_id = c.id " +
            "WHERE m.user_id = #{userId} AND m.delete_time IS NULL AND m.category_id = #{categoryId} " +
            "ORDER BY m.update_time DESC")
    List<Memo> findByCategoryId(@Param("userId") Integer userId, @Param("categoryId") Integer categoryId);
    
    @Select("SELECT m.id, m.user_id, m.category_id, m.title, m.content, m.create_time, m.update_time, m.delete_time, " +
            "c.id as 'category.id', c.name as 'category.name', c.color as 'category.color' " +
            "FROM memo m " +
            "LEFT JOIN category c ON m.category_id = c.id " +
            "WHERE m.user_id = #{userId} AND m.delete_time IS NOT NULL " +
            "ORDER BY m.delete_time DESC")
    List<Memo> findTrashedMemos(Integer userId);
    
    @Delete("DELETE FROM memo WHERE user_id = #{userId} AND delete_time IS NOT NULL")
    int emptyTrash(Integer userId);
}